AVD Assessment and Management Installation Guide

Minimum  Version

This DEX Pack requires SysTrack version 10.1 or higher.

Notes

  • After this DEX Pack is installed, it may take up to 24 hours for data to appear.

  • If you update or reinstall this DEX Pack, you must reassign any Views created below to the SF_AVD Assessment and Management Role.

Import Kit

To use this DEX Pack, the corresponding Kit must be imported to SysTrack.

If you have already imported the DEX Pack directly from the Kits page, the Import Kit step is complete. You may move on to the next step.

If you are viewing this DEX Pack in the Customer Gateway, follow these steps to import this DEX Pack Kit:

  1. On the DEX Pack page, download the DEX Pack ZIP file

  2. In SysTrack, open Kits

  3. Under Local, click Select Kit File

  4. Select the DEX Pack ZIP File

WVD Users Views

The Assessment Summary Dashboard requires you to make a View for the SF_AVD Assessment and Management Role.

  1. Go to Configure > Views

  2. Click the padlock in the upper-right to edit.

  3. Click the plus to add a new View.

  4. Enter the following Settings:

    1. View Name: SF_WVD_Users

    2. Expires in: 30 Days

    3. Existing Category: Custom

    4. When Expired: Overwrite Data

    5. Do not check when overdue by 1 day(s)

    6. Set the Refresh drop-downs to Do not check when overdue by 1 day(s), Inside, 24x7, and Every Day

  5. Copy this SQL query, and paste it under SQL Selection > Generic

  6. Click Test SQL. A Test Success message should appear. If the test fails, the query may have been copied incorrectly.

  7. Click Create View at the top-right.

Copy SF_WVD_Users

Copy 
SELECT
    T0.WGUID,
    GETUTCDATE() AS VWTIME,
    T0.UNAME,
    T0.TOTAL_MIPS,
    T0.TOTAL_MEM,
    T0.TOTAL_MINS,
    T1.ACTIVE_MIPS,
    T1.ACTIVE_MEM,
    T1.ACTIVE_MINS,
    T2.PROFILE_SIZE,
    T3.TOTAL_IMPACT,
    100-((T3.TOTAL_IMPACT/T1.ACTIVE_MINS)*100) AS HEALTH_SCORE,
    T4.TOTAL_TX_BYTES
FROM ((((
    SELECT
        T0.WGUID,
        S0.STRVALUE AS UNAME,
        AVG(T0.PRIV_CPU_MIPS + T0.USER_CPU_MIPS) AS TOTAL_MIPS,
        AVG(T0.MEM_AVG) AS TOTAL_MEM,
        COUNT(T0.WGUID)*10 AS TOTAL_MINS
    FROM SASYSUSERS AS T0
    INNER JOIN SASTRUSER AS S0 ON T0.ACCOUNT_ID = S0.STRINGID
    WHERE S0.STRVALUE NOT LIKE '%SYSTEM%'
    GROUP BY
        T0.WGUID,
        S0.STRVALUE
) AS T0
LEFT JOIN (
    SELECT
        T0.WGUID,
        S0.STRVALUE AS UNAME,
        AVG(T0.PRIV_CPU_MIPS + T0.USER_CPU_MIPS) AS ACTIVE_MIPS,
        AVG(T0.MEM_AVG) AS ACTIVE_MEM,
        COUNT(T0.WGUID)*10 AS ACTIVE_MINS
    FROM SASYSUSERS AS T0
    INNER JOIN SASTRUSER AS S0 ON T0.ACCOUNT_ID = S0.STRINGID
    WHERE
        T0.WUSAGE = 3
        AND S0.STRVALUE NOT LIKE '%SYSTEM%'
    GROUP BY
        T0.WGUID,
        S0.STRVALUE
) AS T1 ON T0.WGUID = T1.WGUID AND T0.UNAME = T1.UNAME)
LEFT JOIN (
    SELECT
        T0.WGUID,
        S0.STRVALUE AS UNAME,
        T0.SIZE_S + T0.SIZE_M + T0.SIZE_L AS PROFILE_SIZE
    FROM (SAFILECOUNTSFOLDERS AS T0
    INNER JOIN SASTRUSER AS S0 ON T0.ACCOUNT_ID = S0.STRINGID)
    INNER JOIN SASTR AS S1 ON T0.FOLDER_ID = S1.STRINGID
    WHERE S1.STRVALUE = 'Profile'
) AS T2 ON T0.WGUID = T2.WGUID AND T0.UNAME = T2.UNAME)
LEFT JOIN (
    SELECT
        T0.WGUID,
        S0.STRVALUE AS UNAME,
        CAST(SUM(WSUMMARY) AS FLOAT) AS TOTAL_IMPACT
    FROM (SASYSUSERS AS T0
    INNER JOIN SAHEALTH AS T1 ON T0.WTIME = T1.WTIME)
    INNER JOIN SASTRUSER AS S0 ON T0.ACCOUNT_ID = S0.STRINGID
    WHERE
        T0.WTYPE = 1
        AND T1.WTYPE = 1
        AND T0.WUSAGE = 3
    GROUP BY
        T0.WGUID,
        S0.STRVALUE
) AS T3 ON T0.WGUID = T3.WGUID AND T0.UNAME = T3.UNAME)
LEFT JOIN (
    SELECT
        T0.WGUID,
        S0.STRVALUE AS UNAME,
        SUM(T0.TX_BYTES) AS TOTAL_TX_BYTES
    FROM SAAPP AS T0
    INNER JOIN SASTRUSER AS S0 ON S0.STRINGID = T0.ACCOUNT_ID
    WHERE
        T0.START_TIME >= GETUTCDATE() - 30
        AND T0.SYSACCOUNT = 0
    GROUP BY
        T0.WGUID,
        S0.STRVALUE
) AS T4 ON T0.WGUID = T4.WGUID AND T0.UNAME = T4.UNAME

WVD Users Hourly Views

The Assessment Summary Dashboard requires you to create a new View:

  1. Go to Configure > Views

  2. Click the padlock in the upper-right to edit.

  3. Click the plus to add a new View.

  4. Enter the following Settings:

    1. View Name: SF_WVD_Users_Hourly

    2. Expires in: 30 Days

    3. Existing Category: General

    4. When Expired: Overwrite Data

    5. Do not check when overdue by 1 day(s)

    6. Set the Refresh drop-downs to Daily, Inside, 24x7, and Every Day

  5. Copy this SQL query, and paste it under SQL Selection > Generic.

  6. Click Test SQL. A Test Success message should appear. If the test fails, the query may have been copied incorrectly.

  7. Click Create View at the top-right.

Copy SF_WVD_Users_Hourly

Copy 
SELECT
    T0.WGUID,
    GETUTCDATE() AS VWTIME,
    S0.STRVALUE AS UNAME,
    'Active' AS WUSAGE,
    DATEADD(HH,DATEDIFF(HH,0,T0.WTIME),0) AS HOTY,
    AVG(PRIV_CPU_MIPS+USER_CPU_MIPS) AS MIPS,
    AVG(MEM_AVG) AS MEM
FROM SASYSUSERS AS T0
INNER JOIN SASTRUSER AS S0 ON T0.ACCOUNT_ID = S0.STRINGID
WHERE
    S0.STRVALUE NOT LIKE '%SYSTEM%'
    AND T0.WTYPE = 1
    AND T0.WUSAGE = 3
GROUP BY
    T0.WGUID,
    S0.STRVALUE,
    DATEADD(HH,DATEDIFF(HH,0,T0.WTIME),0)
UNION
SELECT T0.*
FROM (
    SELECT
        T0.WGUID,
        GETUTCDATE() AS VWTIME,
        S0.STRVALUE AS UNAME,
        'Logged In' AS WUSAGE,
        DATEADD(HH,DATEDIFF(HH,0,T0.WTIME),0) AS HOTY,
        AVG(PRIV_CPU_MIPS+USER_CPU_MIPS) AS MIPS,
        AVG(MEM_AVG) AS MEM
    FROM SASYSUSERS AS T0
    INNER JOIN SASTRUSER AS S0 ON T0.ACCOUNT_ID = S0.STRINGID
    WHERE
        S0.STRVALUE NOT LIKE '%SYSTEM%'
        AND T0.WTYPE = 1
        AND T0.WUSAGE = 1
    GROUP BY
        T0.WGUID,
        S0.STRVALUE,
        DATEADD(HH,DATEDIFF(HH,0,T0.WTIME),0)
) AS T0
LEFT JOIN (
    SELECT DISTINCT
        T0.WGUID,
        S0.STRVALUE AS UNAME,
        DATEADD(HH,DATEDIFF(HH,0,T0.WTIME),0) AS HOTY
    FROM SASYSUSERS AS T0
    INNER JOIN SASTRUSER AS S0 ON T0.ACCOUNT_ID = S0.STRINGID
    WHERE
        S0.STRVALUE NOT LIKE '%SYSTEM%'
        AND T0.WTYPE = 1
        AND T0.WUSAGE = 3
) AS T1 ON T0.WGUID = T1.WGUID AND T0.UNAME = T1.UNAME AND T0.HOTY = T1.HOTY
WHERE T1.HOTY IS NULL

Assign Views

After the SF_WVD_Users and SF_WVD_Users_Hourly have been created, they must be assigned to the SF_AVD Assessment and Management Role:

  1. Go to Configure > Roles

  2. Use the drop-down at the top to select the SF_AVD Assessment and Management Role

  3. Click Views, then check SF_WVD_Users, and SF_WVD_Users_Hourly.

  4. Click Save Changes at the top-right.

Prerequisites

For this AVD DEX Pack, it is necessary that the user has administrative or delegated permissions on both the SysTrack Cloud Portal and the Azure Portal.

TIP: In a Browser, have one tab connected to SysTrack Cloud and another tab open to Azure Portal using the same privileged account.

Other Integrations

SysTrack Configure provides additional configuration settings for interaction with the following external third-party services: